﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

namespace DataAccess.SqlServer
{
    public class SqlServerHocKy: IHocKy
    {
        public HocKy GetHocKy(int oid)
        {
            string sql = @" Select MaQuanLy, TenHocKy, NamHoc, TuNgay, DenNgay 
                            From HocKy 
                            Where Oid = @oid";
            SqlParameter param = new SqlParameter("@oid", oid);

            DataRow row = DataProvider.GetDataRow(sql, param);

            HocKy hocKy = new HocKy();

            hocKy.Oid = oid;
            hocKy.MaQuanLy = row["MaQuanLy"].ToString();
            hocKy.TenHocKy = row["TenHocKy"].ToString();
            hocKy.TuNgay = DateTime.Parse(row["TuNgay"].ToString());
            hocKy.DenNgay = DateTime.Parse(row["DenNgay"].ToString());
            int namHoc;
            if (int.TryParse(row["NamHoc"].ToString(), out namHoc))
            {
                SqlServerNamHoc sqlServerNamHoc = new SqlServerNamHoc();
                hocKy.NamHoc = sqlServerNamHoc.GetNamHoc(namHoc);
            }
            return hocKy;
         }

        public List<HocKy> GetListHocKy(NamHoc namHoc)
        {
            string sql = @" Select Oid, MaQuanLy, TenHocKy, TuNgay, DenNgay 
                            From HocKy 
                            Where NamHoc = @NamHoc";

            SqlParameter param = new SqlParameter("@NamHoc", namHoc.Oid);

            DataTable dt = DataProvider.GetDataTable(sql, param);
            List<HocKy> list = new List<HocKy>();

            foreach (DataRow row in dt.Rows)
            {
                HocKy hocKy = new HocKy();

                hocKy.Oid = int.Parse(row["Oid"].ToString());
                hocKy.MaQuanLy = row["MaQuanLy"].ToString();
                hocKy.TenHocKy = row["TenHocKy"].ToString();
                hocKy.TuNgay = DateTime.Parse(row["TuNgay"].ToString());
                hocKy.DenNgay = DateTime.Parse(row["DenNgay"].ToString());
                hocKy.NamHoc = namHoc;

                list.Add(hocKy);
            }
            return list;
        }
        public List<HocKy> GetListHocKy()
        {
            string sql = @" Select Oid, MaQuanLy, TenHocKy, TuNgay, DenNgay, NamHoc 
                            From HocKy";

            DataTable dt = DataProvider.GetDataTable(sql);
            List<HocKy> list = new List<HocKy>();

            foreach (DataRow row in dt.Rows)
            {
                HocKy hocKy = new HocKy();

                hocKy.Oid = int.Parse(row["Oid"].ToString());
                hocKy.MaQuanLy = row["MaQuanLy"].ToString();
                hocKy.TenHocKy = row["TenHocKy"].ToString();
                hocKy.TuNgay = DateTime.Parse(row["TuNgay"].ToString());
                hocKy.DenNgay = DateTime.Parse(row["DenNgay"].ToString());
                int namHoc;
                if (int.TryParse(row["NamHoc"].ToString(), out namHoc))
                {
                    SqlServerNamHoc sqlServerNamHoc = new SqlServerNamHoc();
                    hocKy.NamHoc = sqlServerNamHoc.GetNamHoc(namHoc);
                }

                list.Add(hocKy);
            }
            return list;
        }

        public int Insert(HocKy hocKy)
        {
            string sql = @" Insert into HocKy(MaQuanLy, TenHocKy, NamHoc, TuNgay, DenNgay)
                            Values(@MaQuanLy, @TenHocKy, @NamHoc, @TuNgay, @DenNgay)";
            SqlParameter[] param = {
                                       new SqlParameter("@MaQuanLy", hocKy.MaQuanLy),
                                       new SqlParameter("@TenHocKy", hocKy.TenHocKy),
                                       new SqlParameter("@NamHoc", hocKy.NamHoc.Oid),
                                       new SqlParameter("@TuNgay", hocKy.TuNgay),
                                       new SqlParameter("@DenNgay", hocKy.DenNgay)
                                   };

            int id = DataProvider.Insert(sql,true, param);
            if (id > 0)
            {
                hocKy.Oid = id;
                return id;
            }
            return -1;
        }

        public int Update(HocKy hocKy)
        {
            string sql = @" Update HocKy
                            Set MaQuanLy = @MaQuanLy,
                                TenHocKy = @TenHocKy,
                                NamHoc = @NamHoc,
                                TuNgay = @TuNgay,
                                DenNgay = @DenNgay
                            Where Oid = @Oid";

            SqlParameter[] param = {
                                       new SqlParameter("@Oid", hocKy.Oid),
                                       new SqlParameter("@MaQuanLy", hocKy.MaQuanLy),
                                       new SqlParameter("@TenHocKy", hocKy.TenHocKy),
                                       new SqlParameter("@NamHoc", hocKy.NamHoc.Oid),
                                       new SqlParameter("@TuNgay", hocKy.TuNgay),
                                       new SqlParameter("@DenNgay", hocKy.DenNgay)
                                   };
            return DataProvider.Update(sql, param);
        }

        public int Delete(HocKy hocKy)
        {
            string sql = @" Delete From HocKy Where Oid = @Oid";

            SqlParameter param = new SqlParameter("@Oid", hocKy.Oid);

            return DataProvider.Update(sql, param);
        }



        public int Save(HocKy obj)
        {
            throw new NotImplementedException();
        }


    }
}
